package dao;

import conn.ConData;
import model.Suppliecharacter;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SuppliecharacterDao {
    Connection con=null;//连接数据库对象  默认值为空
    ConData conn=new ConData();//连接数据库驱动

    /**
     * 给suppliecharacter表添加数据
     * @param suppliecharacter 实体
     * @return
     */
    public int add(Suppliecharacter suppliecharacter){//参数类型 参数名  向方法里传参
        int num=0;//添加不成功
        //创建sql语句
        String sql="INSERT INTO suppliecharacter (name1,sex,phone,limit1,fromsupplier,managername,state,creater,updater,password1,email,workplace,servicearea,duty,educationbackground,personid,remark) VALUE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        //创建执行sql的对象A
        PreparedStatement ps;//带参数的执行sql语句对象
        try {
            //输出suppliecharacter的创建人方法的值
            System.out.println(suppliecharacter.getUserId());
            //调用连接数据库的方法
            con=conn.getCon();
            //返回一个PreparedStatement实例
            ps=con.prepareStatement(sql);


            ps.setString(1 , suppliecharacter.getName1());
            ps.setString(2, suppliecharacter.getSex());
            ps.setString(3 , suppliecharacter.getPhone());
            ps.setString(4 , suppliecharacter.getLimit1());
            ps.setString(5 , suppliecharacter.getFromsupplier());
            ps.setString(6 , suppliecharacter.getManagername());
            ps.setString(7 , suppliecharacter.getState());
            ps.setString(8 , suppliecharacter.getCreater());
           /* ps.setDate(8 , suppliecharacter.getCreatetime());*/
            ps.setString(9 , suppliecharacter.getUpdater());
            /* ps.setDate(8 , suppliecharacter.getUpdatetime());*/
           /* ps.setInt(10 , suppliecharacter.getUserId());*/
            ps.setString(10 , suppliecharacter.getPassword1());
            ps.setString(11 , suppliecharacter.getEmail());
            ps.setString(12 , suppliecharacter.getWorkplace());
            ps.setString(13 , suppliecharacter.getServicearea());
            ps.setString(14 , suppliecharacter.getDuty());
            ps.setString(15 , suppliecharacter.getEducationbackground());
            ps.setString(16 , suppliecharacter.getPersonid());
            ps.setString(17 , suppliecharacter.getRemark());


            num=ps.executeUpdate();//执行sql语句
            //关闭执行对象
            ps.close();
            con.close();
        }catch(SQLException e){
            e.printStackTrace();
        }
        return num;
    }


    /**
     * 根据主键更新信息
     * @param suppliecharacter
     * @return
     */

    public int update(Suppliecharacter suppliecharacter){
        int isSuccess=0;//修改未成功
        String sql="UPDATE suppliecharacter SET name1=? WHERE suppliecharacterid=?";//修改语句
        /*name1=?,sex=?,phone=?,limit1=?,fromsupplier=?,managername=?,state=?,creater=?,updater=?,password1=?,email=?,workplace=?,servicearea=?,duty=?,educationbackground=?,personid=?,remark=?*/
        PreparedStatement ps;
        try{
            //调用连接数据库的方法
            con=conn.getCon();
            //返回一个PrepareStatement实例
            ps=con.prepareStatement(sql);


            ps.setString(1 , suppliecharacter.getName1());
            /*ps.setString(2, suppliecharacter.getSex());
            ps.setString(3 , suppliecharacter.getPhone());
            ps.setString(4 , suppliecharacter.getLimit1());
            ps.setString(5 , suppliecharacter.getFromsupplier());
            ps.setString(6 , suppliecharacter.getManagername());
            ps.setString(7 , suppliecharacter.getState());
            ps.setString(8 , suppliecharacter.getCreater());*/
            /* ps.setDate(8 , suppliecharacter.getCreatetime());*/
            /*ps.setString(9 , suppliecharacter.getUpdater());*/
            /* ps.setDate(8 , suppliecharacter.getUpdatetime());*/
            /* ps.setInt(10 , suppliecharacter.getUserId());*/
            /*ps.setString(10 , suppliecharacter.getPassword1());
            ps.setString(11 , suppliecharacter.getEmail());
            ps.setString(12 , suppliecharacter.getWorkplace());
            ps.setString(13 , suppliecharacter.getServicearea());
            ps.setString(14 , suppliecharacter.getDuty());
            ps.setString(15 , suppliecharacter.getEducationbackground());
            ps.setString(16 , suppliecharacter.getPersonid());
            ps.setString(17 , suppliecharacter.getRemark());*/
            ps.setInt(2 , suppliecharacter.getSuppliecharacterid());

            //执行修改
            isSuccess=ps.executeUpdate();
            //关闭执行对象
            ps.close();
            con.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return isSuccess;
    }


    /*
     * 根据主键删除信息
     * */

    public int del(Suppliecharacter suppliecharacter){
        int isDel=0;//删除未成功
        //创建执行sql语句
        String sql="DELETE FROM suppliecharacter WHERE suppliecharacterid=?";
        //创建执行sql的对象
        PreparedStatement ps;
        try{
            con=conn.getCon();
            ps=con.prepareStatement(sql);
            //设置删除条件
            ps.setInt(1, suppliecharacter.getSuppliecharacterid());
            //删除
            isDel=ps.executeUpdate();
            //关闭执行对象
            ps.close();
            //关闭连接对象
            con.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return isDel;
    }



    /*
     * 根据查询条件返回查询结果
     *
     * */
    public List<Suppliecharacter> getAll(Suppliecharacter suppliecharacter){
        System.out.println("进入查询方法");
        List<Suppliecharacter> listSuppliecharacter=new ArrayList<Suppliecharacter>();
        //创建sql字符串
        String str="SELECT * FROM suppliecharacter ";
        int Suppliecharacterid=0;
        con=conn.getCon();
        try{
            //创建执行sql的对象
            PreparedStatement ps=con.prepareStatement(str);
            System.out.println("查询语句:"+str);
            //设置查询条件
            //返回查询记录结果的记录集
            ResultSet rs=ps.executeQuery();

            //将记录集存入集合当中
            while(rs.next()){
                //声明一个实体对象
                Suppliecharacter b=new Suppliecharacter();
                //给对象赋值
                b.setSuppliecharacterid(rs.getInt(1));
                b.setName1(rs.getString(2));
                b.setSex(rs.getString(3));
                b.setPhone(rs.getString(4));
                b.setLimit1(rs.getString(5));
                b.setFromsupplier(rs.getString(6));
                b.setManagername(rs.getString(7));
                b.setState(rs.getString(8));
                b.setCreater(rs.getString(9));
                b.setCreatetime(rs.getString(10));
                b.setUpdater(rs.getString(11));
                b.setUpdatetime(rs.getString(12));
                b.setUserId(rs.getString(13));
                b.setPassword1(rs.getString(14));
                b.setEmail(rs.getString(15));
                b.setWorkplace(rs.getString(16));
                b.setServicearea(rs.getString(17));
                b.setDuty(rs.getString(18));
                b.setEducationbackground(rs.getString(19));
                b.setPersonid(rs.getString(20));
                b.setRemark(rs.getString(21));
                //将对象实体添加到集合中
                listSuppliecharacter.add(b);

            }
            //关闭记录集
            rs.close();
            //关闭执行的sql对象
            ps.close();
            //关闭连接
            con.close();

        }catch(SQLException e){
            e.printStackTrace();
        }
        System.out.println(listSuppliecharacter);
        return listSuppliecharacter;
    }


    public List<Suppliecharacter> getDan(Suppliecharacter suppliecharacter){
        System.out.println("进入单项查询方法");
        List<Suppliecharacter> listSuppliecharacter=new ArrayList<Suppliecharacter>();
        //创建sql字符串
        String str="SELECT * FROM suppliecharacter WHERE name1=?";
        int Suppliecharacterid=0;
        con=conn.getCon();
        try{
            //创建执行sql的对象
            PreparedStatement ps=con.prepareStatement(str);
            System.out.println("查询语句:"+str);
            //设置查询条件
            ps.setString(1,suppliecharacter.getName1());
            //返回查询记录结果的记录集
            ResultSet rs=ps.executeQuery();

            //将记录集存入集合当中
            while(rs.next()){
                //声明一个实体对象
                Suppliecharacter b=new Suppliecharacter();
                //给对象赋值
                b.setSuppliecharacterid(rs.getInt(1));
                b.setName1(rs.getString(2));
                b.setSex(rs.getString(3));
                b.setPhone(rs.getString(4));
                b.setLimit1(rs.getString(5));
                b.setFromsupplier(rs.getString(6));
                b.setManagername(rs.getString(7));
                b.setState(rs.getString(8));
                b.setCreater(rs.getString(9));
                b.setCreatetime(rs.getString(10));
                b.setUpdater(rs.getString(11));
                b.setUpdatetime(rs.getString(12));
                b.setUserId(rs.getString(13));
                b.setPassword1(rs.getString(14));
                b.setEmail(rs.getString(15));
                b.setWorkplace(rs.getString(16));
                b.setServicearea(rs.getString(17));
                b.setDuty(rs.getString(18));
                b.setEducationbackground(rs.getString(19));
                b.setPersonid(rs.getString(20));
                b.setRemark(rs.getString(21));
                //将对象实体添加到集合中
                listSuppliecharacter.add(b);

            }
            //关闭记录集
            rs.close();
            //关闭执行的sql对象
            ps.close();
            //关闭连接
            con.close();

        }catch(SQLException e){
            e.printStackTrace();
        }
        System.out.println(listSuppliecharacter);
        return listSuppliecharacter;
    }



    public List<Suppliecharacter> getAddc(Suppliecharacter suppliecharacter){
        System.out.println("进入更改按id查询方法");
        List<Suppliecharacter> listSuppliecharacter=new ArrayList<Suppliecharacter>();
        //创建sql字符串
        String str="SELECT * FROM suppliecharacter WHERE suppliecharacterid=?";
        con=conn.getCon();
        try{
            //创建执行sql的对象
            PreparedStatement ps=con.prepareStatement(str);
            System.out.println("查询语句:"+str);
            //设置查询条件
            ps.setInt(1,suppliecharacter.getSuppliecharacterid());
            //返回查询记录结果的记录集
            ResultSet rs=ps.executeQuery();

            //将记录集存入集合当中
            while(rs.next()){
                //声明一个实体对象
                Suppliecharacter b=new Suppliecharacter();
                //给对象赋值
                b.setSuppliecharacterid(rs.getInt(1));
                b.setName1(rs.getString(2));
                b.setSex(rs.getString(3));
                b.setPhone(rs.getString(4));
                b.setLimit1(rs.getString(5));
                b.setFromsupplier(rs.getString(6));
                b.setManagername(rs.getString(7));
                b.setState(rs.getString(8));
                b.setCreater(rs.getString(9));
                b.setCreatetime(rs.getString(10));
                b.setUpdater(rs.getString(11));
                b.setUpdatetime(rs.getString(12));
                b.setUserId(rs.getString(13));
                b.setPassword1(rs.getString(14));
                b.setEmail(rs.getString(15));
                b.setWorkplace(rs.getString(16));
                b.setServicearea(rs.getString(17));
                b.setDuty(rs.getString(18));
                b.setEducationbackground(rs.getString(19));
                b.setPersonid(rs.getString(20));
                b.setRemark(rs.getString(21));
                //将对象实体添加到集合中
                listSuppliecharacter.add(b);

            }
            //关闭记录集
            rs.close();
            //关闭执行的sql对象
            ps.close();
            //关闭连接
            con.close();

        }catch(SQLException e){
            e.printStackTrace();
        }
        System.out.println(listSuppliecharacter);
        return listSuppliecharacter;
    }
}
